package com.tic.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;

import utiles.AppProperties;
import utiles.ConexionPool;
import utiles.utilitarios;


import com.portal.dto.ComboDTO;
import com.tic.dto.UsuarioDTO;
import com.tic.vo.UserVO;


public class UsuarioDAO {
	private static Logger log = Logger.getLogger(PerfilDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	String horaActual = utilitarios.getHoraActual();
	/*
	public List<UsuarioDTO> getUsuarioById(String IdUser) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();

		List<UsuarioDTO> listUserPerf = new ArrayList<UsuarioDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_USUARIO.getUsuarioById(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, IdUser);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					UsuarioDTO sopMA = new UsuarioDTO();
					sopMA.setId(rs.getString(1));
					sopMA.setRut(rs.getString(2));
					sopMA.setDv(rs.getString(3));
					sopMA.setNombre(rs.getString(4));
					sopMA.setApellido(rs.getString(5));
					sopMA.setUsuario(rs.getString(6));
					sopMA.setPassword(rs.getString(7));
					sopMA.setSede(rs.getString(8));
					sopMA.setArea(rs.getString(9));
					sopMA.setCaja(rs.getString(10));
					sopMA.setMeson(rs.getString(11));
					sopMA.setKper(rs.getString(12));
					sopMA.setKusr(rs.getString(13));
					sopMA.setKsuc(rs.getString(14));
					sopMA.setPass(rs.getString(15));
					sopMA.setEmail(rs.getString(17));
					sopMA.setActivo(rs.getString(18));
					sopMA.setSmtpServer(rs.getString(19));
					sopMA.setSmtpAuth(rs.getString(20));
					sopMA.setSmtpPass(rs.getString(21));
					sopMA.setSmtpPort(rs.getString(22));
					sopMA.setSmtpStls(rs.getString(23));
					listUserPerf.add(sopMA);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error UsuarioDAO.getUsuarioById SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}
	public List<ComboDTO> getPerfilSoftram() throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();

		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_USUARIO.getPerfilSoftram(?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.registerOutParameter(1, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(1);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error UsuarioDAO.getPerfilSoftram SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}

	 public List<UsuarioDTO> setGrabarUser(UserVO userVO) throws SQLException, IOException{
			List<UsuarioDTO> listIDMovTotal = new ArrayList<UsuarioDTO>();
			Connection conn = ConexionPool.getConnectionPool();
					try {
						String query = "{ call "+pkgVar+".PKG_PER_USUARIO.setGrabarUser(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, userVO.getId());
						stmt.setString(2, userVO.getRut());
						stmt.setString(3, userVO.getDv());
						stmt.setString(4, userVO.getNombre());
						stmt.setString(5, userVO.getApellido());
						stmt.setString(6, userVO.getUsuario());
						stmt.setString(7, userVO.getPassword());
						stmt.setString(8, userVO.getSede());
						stmt.setString(9, userVO.getArea());
						stmt.setString(10, userVO.getKper());
						stmt.setString(11, userVO.getEmail());
						stmt.setString(12, userVO.getActivo());
						stmt.setString(13, userVO.getSmtpServer());
						stmt.setString(14, userVO.getSmtpAuth());
						stmt.setString(15, userVO.getSmtpPass());
						stmt.setString(16, userVO.getSmtpPort());
						stmt.setString(17, userVO.getSmtpStls());
						stmt.registerOutParameter(18, OracleTypes.CURSOR);
						stmt.execute();
						ResultSet rs = (ResultSet)stmt.getObject(18);
						while (rs.next()) {
							UsuarioDTO estGrab = new UsuarioDTO();
							estGrab.setId(rs.getString(1));
							estGrab.setNombre(rs.getString(2));
							estGrab.setApellido(rs.getString(3));
							listIDMovTotal.add(estGrab);					
						}
						stmt.close();
					} catch (SQLException ex) {  
						log.error("Error UsuarioDAO.setGrabarUser SQLException: " +ex.getMessage());
						System.out.println(ex.getMessage());
					} finally {
						//ConexionPool.desconectaPool(conn);
						conn.close();
						conn = null;
					}
					return listIDMovTotal;
		}

		public List<ComboDTO> getValidaUser(String usuario, String iduser, String nombre, String apellido) throws SQLException, IOException{
			Connection conn = ConexionPool.getConnectionPool();
			List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
				try {
					String query = "{ call "+pkgVar+".PKG_PER_USUARIO.getValidaUser(?,?,?,?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, usuario);
					stmt.setString(2, iduser);
					stmt.setString(3, nombre);
					stmt.setString(4, apellido);
					stmt.registerOutParameter(5, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(5);
					while (rs.next()) {
						ComboDTO perfUSR = new ComboDTO();
						perfUSR.setId(rs.getString(1));
						perfUSR.setDesc(rs.getString(2));
						perfUSR.setId2(rs.getString(3));
						perfUSR.setDesc2(rs.getString(4));
						listUserPerf.add(perfUSR);
						//System.out.println(rs.getString(1)+"-"+rs.getString(2));
		            }
		            rs.close();
		            stmt.close();
		        } catch (SQLException ex) {  
					log.error("Error UsuarioDAO.getValidaUser SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					//ConexionPool.desconectaPool(conn);
					conn.close();
					conn = null;
				}
		        return listUserPerf;		
			}

		public List<ComboDTO> getRolByIdUser(String iduser) throws SQLException, IOException{
			Connection conn = ConexionPool.getConnectionPool();

			List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
				try {
					String query = "{ call "+pkgVar+".PKG_PER_USUARIO.getRolByIdUser(?,?) }"; 	       
					CallableStatement  stmt = conn.prepareCall(query);
					stmt.setString(1, iduser);
					stmt.registerOutParameter(2, OracleTypes.CURSOR);
					stmt.execute();
					ResultSet rs = (ResultSet)stmt.getObject(2);
					while (rs.next()) {
						ComboDTO perfUSR = new ComboDTO();
						perfUSR.setId(rs.getString(1));
						perfUSR.setDesc(rs.getString(2));
						listUserPerf.add(perfUSR);
						//System.out.println(rs.getString(1)+"-"+rs.getString(2));
		            }
		            rs.close();
		            stmt.close();
		        } catch (SQLException ex) {  
					log.error("Error UsuarioDAO.getRolByIdUser SQLException: " +ex.getMessage());
					System.out.println(ex.getMessage());
				} finally {
					//ConexionPool.desconectaPool(conn);
					conn.close();
					conn = null;
				}
		        return listUserPerf;		
			}
*/
}